package com.newbsd.web.maventest.dao.impl;



	import java.sql.Connection;
	import java.sql.PreparedStatement;
	import java.sql.ResultSet;
	import java.sql.SQLException;
	import java.sql.Statement;
	import java.util.ArrayList;
	import java.util.List;

	import javax.naming.Context;
	import javax.naming.InitialContext;
	import javax.naming.NamingException;
	import javax.sql.DataSource;
	
    import com.newbsd.web.maventest.dao.TicketDaohx;
    import com.newbsd.web.maventest.pojo.Ticket;
	import com.newbsd.web.maventest.pojo.User;


	/**
	 * 
	 * @author 韩晓
	 *
	 */
	public class TicketDaoImplhx  implements TicketDaohx {


		  //车票分页
			@Override
			public List<Ticket> allTicket(int pagesize,int rows ) {
				// TODO Auto-generated method stubDaoUtil.getInstance().getList(sqlId, clazz, param)
				List<Ticket> tickets = new ArrayList<>();
				Connection connection = null;
				
				try {
					Context context = new InitialContext();
					DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
					connection = datasource.getConnection();
				} catch (NamingException | SQLException e) {
					e.printStackTrace();
				}
				
				if (null != connection) {
					ResultSet result = null;
					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement("select * from ticket limit ?,?");
						ps.setInt(1, (pagesize-1)*rows);
						ps.setInt(2, rows*pagesize);
						result = ps.executeQuery();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
					if (null != result) {
						try {
							Ticket ticket = null;
							while(result.next()) {
								ticket = new Ticket();
								ticket.setTickedid(result.getInt("tickedid"));
								ticket.setTicketnum(result.getInt("ticketnum"));
								ticket.setDestination(result.getString("destination"));
								ticket.setOrigin(result.getString("origin"));
								ticket.setPrice(result.getDouble("price"));
								ticket.setTrainno(result.getString("trainno"));
								
								tickets.add(ticket);
								
								
							}
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				}
				
				return tickets;
				
				
			}
          //增加车票
			@Override
			public Integer addTicket(Ticket ticket) {
	            Connection connection = null;
				
				try {
					Context context = new InitialContext();
					DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
					connection = datasource.getConnection();
				} catch (NamingException | SQLException e) {
					e.printStackTrace();
				}
				if (null != connection) {
					ResultSet result = null;
					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement("insert into ticket(destination,origin,price,ticketnum,trainno) values(?,?,?,?,?)");
						ps.setString(1, ticket.getDestination());
						ps.setString(2, ticket.getOrigin());
						System.out.println(ticket.getPrice());
						ps.setDouble(3, ticket.getPrice());
						
						//ps.setInt(4, ticket.getTickedid());
						ps.setInt(4, ticket.getTicketnum());
						ps.setString(5, ticket.getTrainno());
						ps.execute();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
				
				return 1;
			}

		   //更新车票信息
			@Override
			public void updateTicket(Ticket ticket) {
				Connection connection = null;
				
				
				try {
					Context context = new InitialContext();
					DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
					connection = datasource.getConnection();
				} catch (NamingException | SQLException e) {
					e.printStackTrace();
				}
				if (null != connection) {
					
					ResultSet result = null;
					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement("update ticket set destination= ?,origin=?,price=?,ticketnum=?,trainno=? where tickedid=?");
						ps.setString(1, ticket.getDestination());
						ps.setString(2, ticket.getOrigin());
						ps.setDouble(3, ticket.getPrice());
						
						ps.setInt(4, ticket.getTicketnum());
						ps.setString(5, ticket.getTrainno());
						ps.setInt(6, ticket.getTickedid());
						ps.execute();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}	
				
			}
           //删除车票信息
			@Override
			public void delTicket(int id) {
				Connection connection = null;
				try {
					Context context = new InitialContext();
					DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
					connection = datasource.getConnection();
				} catch (NamingException | SQLException e) {
					e.printStackTrace();
				}
				if (null != connection) {
					Ticket  ticket = null;
					ResultSet result = null;
					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement("delete   from ticket where tickedid=?");
						
						ps.setInt(1, id);
						ps.execute();
					
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}	
				
			}
           //查找所有车票
			@Override
			public List<Ticket> allTicket() {
				List<Ticket> tickets = new ArrayList<>();
				Connection connection = null;
				
				try {
					Context context = new InitialContext();
					DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
					connection = datasource.getConnection();
				} catch (NamingException | SQLException e) {
					e.printStackTrace();
				}
				
				if (null != connection) {
					ResultSet result = null;
					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement("select * from ticket ");
						
						result = ps.executeQuery();
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					
					if (null != result) {
						try {
							Ticket ticket = null;
							while(result.next()) {
								ticket = new Ticket();
								ticket.setTickedid(result.getInt("tickedid"));
								ticket.setTicketnum(result.getInt("ticketnum"));
								ticket.setDestination(result.getString("destination"));
								ticket.setOrigin(result.getString("origin"));
								ticket.setPrice(result.getDouble("price"));
								ticket.setTrainno(result.getString("trainno"));
							
								tickets.add(ticket);
								
								
							}
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
					}
				}
				
				return tickets;
			}
            //查出所有的条数
			@Override
			public Integer getTotals() {
				
				
				Connection connection = null;
				int total =0;
				try {
					Context context = new InitialContext();
					DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/maventest");
					connection = datasource.getConnection();
				} catch (NamingException | SQLException e) {
					e.printStackTrace();
				}
				
				if (null != connection) {
					ResultSet result = null;
					PreparedStatement ps = null;
					try {
						ps = connection.prepareStatement("select count(*) total from ticket ");
						
						result = ps.executeQuery();
						while (result.next()) {
							total = result.getInt("total");
						}
						
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
				}
				return total;
			}

			

	}   




	
	
	
	


